﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace kwzx.Common
{


    public class DBHelperEX
    {
        private SqlConnection conn = null;
        /// <summary>
        /// 打开连接
        /// </summary>
        public void Open()
        {
            if (conn == null)
            {
                 conn = new SqlConnection(@"Data Source=10.35.5.37;Initial Catalog=scm_main;Persist Security Info=True;User ID=sa;Password=sa");
                //conn = new SqlConnection(ConfigurationManager.ConnectionStrings["scm_mainEntities"].ConnectionString);

            }
            if (conn.State == System.Data.ConnectionState.Closed)
                conn.Open();
        }
        /// <summary>
        /// 关闭连接
        /// </summary>
        public void Close()
        {
            if (conn != null)
                conn.Close();
        }
        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            if (conn != null)
            {
                conn.Dispose();
                conn = null;
            }
        }
        /// <summary>
        /// 创建参数
        /// </summary>
        /// <returns></returns>
        public SqlParameter MakePara(string paraName, SqlDbType dbType, int size, ParameterDirection direction, object value)
        {
            SqlParameter para;
            if (size > 0)
                para = new SqlParameter(paraName, dbType, size);
            else
                para = new SqlParameter(paraName, dbType);
            if (direction != ParameterDirection.Output && value != null)
                para.Value = value;
            return para;
        }
        /// <summary>
        /// 创建输入输出参数
        /// </summary>
        /// <param name="paraName"></param>
        /// <param name="dbType"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        public SqlParameter MakeInputOutputPara(string paraName, SqlDbType dbType, int size, object value)
        {
            SqlParameter para = MakePara(paraName, dbType, size, ParameterDirection.InputOutput, value);
            return para;
        }
        /// <summary>
        /// 创建输出参数
        /// </summary>
        /// <param name="paraName"></param>
        /// <param name="dbType"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        public SqlParameter MakeOutPara(string paraName, SqlDbType dbType, int size)
        {
            SqlParameter para = MakePara(paraName, dbType, size, ParameterDirection.Output, null);
            return para;
        }
        /// <summary>
        /// 创建输入参数
        /// </summary>
        /// <param name="paraName"></param>
        /// <param name="dbType"></param>
        /// <param name="size"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public SqlParameter MakeInPara(string paraName, SqlDbType dbType, int size, object value)
        {
            SqlParameter para = MakePara(paraName, dbType, size, ParameterDirection.Input, value);
            return para;
        }
        /// <summary>
        /// 创建command对象
        /// </summary>
        /// <returns></returns>
        public SqlCommand MakeCommand(string procName, SqlParameter[] paras)
        {
            Open();
            //SqlCommand command = conn.CreateCommand();
            //command.CommandText = procName;或者
            SqlCommand command = new SqlCommand(procName, conn);
            command.CommandType = CommandType.StoredProcedure;
            if (paras != null)
            {
                foreach (SqlParameter item in paras)
                {
                    command.Parameters.Add(item);
                }
            }
            //加入返回参数
            command.Parameters.Add(
                new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null)
                );
            return command;
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public int ExecProc(string procName, SqlParameter[] paras)
        {
            SqlCommand command = MakeCommand(procName, paras);
            command.ExecuteNonQuery();
            int returnValue = (int)command.Parameters["ReturnValue"].Value;
            Close();
            return returnValue;
        }

        public void ExecProc(string procName, SqlParameter[] paras, out SqlDataReader dr)
        {
            SqlCommand command = MakeCommand(procName, paras);
            dr = command.ExecuteReader(CommandBehavior.CloseConnection);
        }

        public void ExecProc(string procName, out SqlDataReader dr)
        {
            SqlCommand command = MakeCommand(procName, null);
            dr = command.ExecuteReader(CommandBehavior.CloseConnection);
        }
    }



}
